package net.esj.basic.dao.ann;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.springframework.beans.factory.InitializingBean;

import net.esj.basic.dao.ann.component.Container;
import net.esj.basic.dao.ann.component.column.AbstractColumn;
import net.esj.basic.dao.ann.component.column.CompositePrimaryKey;
import net.esj.basic.dao.ann.component.column.DefaultColumn;
import net.esj.basic.dao.ann.component.column.DefaultPrimaryKey;
import net.esj.basic.dao.ann.component.column.ForeignKeyColumn;
import net.esj.basic.dao.ann.component.column.PrimaryKey;
import net.esj.basic.dao.ann.component.column.PrimaryKeyColumn;
import net.esj.basic.dao.ann.component.table.AbstractTable;
import net.esj.basic.dao.ann.component.table.DefaultTable;
import net.esj.basic.dao.ann.utils.TypeResolver;
import net.esj.basic.utils.GetClassLoader;

public class AnnManager implements InitializingBean{
	
	private static String driver;
	private static String driverUrl;
	private static String user;
	private static String password;

	public static Map drivers = new HashMap();
	
	public static Container container;
	
	@Override
	public void afterPropertiesSet() throws Exception {
		MappingConnectionRunnable runnable = new MappingConnectionRunnable();
		runnable.run();
	}
	
	public String getDriver() {
		return driver;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getDriverUrl() {
		return driverUrl;
	}

	public void setDriverUrl(String driverUrl) {
		this.driverUrl = driverUrl;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}



	public void setPassword(String password) {
		this.password = password;
	}



	public static Connection getConnection(){
		Driver driverObject =  (Driver) drivers.get(driver);
		if(null == driverObject){
			try {
				Class driverClass =GetClassLoader.fromContext().loadClass(driver) ;
				Object tObj = driverClass.newInstance();
				driverObject = (Driver) tObj;
				drivers.put(driver, driverObject);
				Properties prop = new Properties();
				prop.setProperty("user", user);
				prop.setProperty("password", password);
				Connection con = driverObject.connect(driverUrl, prop);
				return con;
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		 return null;
	}
	
	public static int getTableCount (DatabaseMetaData metadata, String schemaPattern, String tablePattern) throws SQLException {
		String[] names = {"TABLE"};
		String _schema = null;
		String _table = null;
		if (null != schemaPattern && schemaPattern.trim().length() > 0)
			_schema = schemaPattern.trim();
		else
			_schema=null;
		if (null != tablePattern && tablePattern.trim().length() > 0)
			_table = tablePattern.trim();
		else
			_table=null;
		ResultSet rs = null;
		try {
			int count = 0;
			rs = metadata.getTables(null, _schema, _table, names);
			Map tables = new HashMap();
			String TABLE_NAME = "TABLE_NAME";
			String TABLE_TYPE = "TABLE_TYPE";
			String SYSTEM = "SYSTEM";
			while (rs.next()) { 
				String tableType = rs.getString(TABLE_TYPE);
				if (null == tableType || tableType.toUpperCase().indexOf(SYSTEM) < 0) {
					count++;
				}
			}
			return count;
		}
		finally {
			if (null != rs) rs.close();
		}
	}
	
	public static void getTables (Container container, DatabaseMetaData metadata) throws SQLException {
		String[] names = {"TABLE"};
		ResultSet rs = null;
		try {
			rs = metadata.getTables(null, null, null, names);
			Map tables = new HashMap();
			String TABLE_NAME = "TABLE_NAME";
			String TABLE_TYPE = "TABLE_TYPE";
			String SYSTEM = "SYSTEM";
			while (rs.next()) { 
				AbstractTable table = new DefaultTable(container,rs.getString(TABLE_NAME)); 
				String tableType = rs.getString(TABLE_TYPE);
				if (null == tableType || tableType.toUpperCase().indexOf(SYSTEM) < 0) {
					tables.put(table.getTableName(), table);
				}
			}
			container.setTables(tables);
		}
		finally {
			if (null != rs) rs.close();
		}
	}
	
	public static void populateTableData (DatabaseMetaData metadata, Container container) throws SQLException {
		Map tables = container.getTables();
		for (Iterator i=tables.values().iterator(); i.hasNext(); ) {
			AbstractTable table = (AbstractTable) i.next();
			readTableColumns(metadata, table);
		}
		for (Iterator i=tables.values().iterator(); i.hasNext(); ) {
			AbstractTable table = (AbstractTable) i.next();
			readTablePK(metadata, table);
		}
		for (Iterator i=tables.values().iterator(); i.hasNext(); ) {
			AbstractTable table = (AbstractTable) i.next();
			readTableFK(metadata, table);
		}
	}
	
	private static void readTableColumns(DatabaseMetaData meta, AbstractTable table) throws SQLException { 
		ResultSet columns = null;
		try {
			columns = meta.getColumns(null, "%", table.getTableName(), "%"); 
			while (columns.next()) { 
				AbstractColumn newColumn = readColumn(meta, columns, DefaultColumn.class);
				table.notifyColumn(newColumn);
			}
		}
		finally {
			if (null != columns) columns.close();
		}
	}
	
	private static void readTablePK(DatabaseMetaData meta, AbstractTable table){
		ResultSet keys = null;
		try {
			keys = meta.getPrimaryKeys(null, null, table.getTableName());
			List<PrimaryKeyColumn> columns = new ArrayList<PrimaryKeyColumn>();
			while(keys.next()){
				String tableName = keys.getString("TABLE_NAME");
				String columnName = keys.getString("COLUMN_NAME");
				AbstractColumn column = table.getColumns().get(columnName);
				if(null!=column){
					PrimaryKeyColumn pkc =new PrimaryKeyColumn(column); 
					columns.add(pkc);
					table.getColumns().put(pkc.getColumnName(), pkc);
				}
			}
			PrimaryKey pk = null;
			if(columns.size()>1){
				pk = new CompositePrimaryKey(columns);
			}else if(columns.size()==1){
				pk = new  DefaultPrimaryKey(columns.get(0));
			}
			table.setPrimaryKey(pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void readTableFK(DatabaseMetaData meta, AbstractTable table){
		ResultSet keys = null;
		try {
			keys = meta.getImportedKeys(null, null, table.getTableName());
			while(keys.next()){
				ForeignKeyColumn column = (ForeignKeyColumn) readColumn(meta, keys, ForeignKeyColumn.class);
				table.getColumns().put(column.getColumnName(), column);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static AbstractColumn readColumn(DatabaseMetaData meta,ResultSet set,Class columnClass){
		String columnName ="";
		AbstractColumn column  =null;
		try {
			columnName = set.getString("COLUMN_NAME");
			String datatype = set.getString("TYPE_NAME");
			Class javaClass = TypeResolver.resolveType(datatype, false);
			int datasize = set.getInt("COLUMN_SIZE"); 
			int digits = set.getInt("DECIMAL_DIGITS"); 
			int nullable = set.getInt("NULLABLE");
			String defaultValue = set.getString("COLUMN_DEF");
			Constructor constructor= columnClass.getConstructor(new Class[]{String.class,Class.class,Integer.class,Object.class,boolean.class});
			column = (AbstractColumn) constructor.newInstance(new Object[]{columnName,javaClass,datasize,defaultValue,nullable>0});
		} catch (SQLException e) {
			System.out.println("错误行："+columnName);
			e.printStackTrace();
		} catch (SecurityException e) {
			System.out.println("错误行："+columnName);
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			System.out.println("错误行："+columnName);
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			System.out.println("错误行："+columnName);
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} 
		return column;
	}
	
	private static String checkName (String s) {
		if (null == s) return null;
		s = s.replaceAll("`", "");
		return s;
	}

}

